package com.ruoyi.business.service.impl;

import com.ruoyi.business.domain.BusContract;
import com.ruoyi.business.domain.BusContractDetail;
import com.ruoyi.business.domain.BusExportContract;
import com.ruoyi.business.domain.BusExportCustomer;
import com.ruoyi.business.mapper.BusContractMapper;
import com.ruoyi.business.service.IBusContractService;
import com.ruoyi.common.annotation.Excel;
import com.ruoyi.common.config.RuoYiConfig;
import com.ruoyi.common.core.domain.AjaxResult;
import com.ruoyi.common.core.text.Convert;
import com.ruoyi.common.exception.UtilException;
import com.ruoyi.common.utils.DateUtils;
import com.ruoyi.common.utils.DictUtils;
import com.ruoyi.common.utils.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 合同Service业务层处理
 * 
 * @author ruoyi
 * @date 2021-09-02
 */
@Service
public class BusContractServiceImpl implements IBusContractService 
{
    @Autowired
    private BusContractMapper busContractMapper;

    /**
     * 查询合同
     * 
     * @param contractId 合同主键
     * @return 合同
     */
    @Override
    public BusContract selectBusContractByContractId(Long contractId)
    {
        return busContractMapper.selectBusContractByContractId(contractId);
    }

    /**
     * 查询合同列表
     * 
     * @param busContract 合同
     * @return 合同
     */
    @Override
    public List<BusContract> selectBusContractList(BusContract busContract)
    {
        return busContractMapper.selectBusContractList(busContract);
    }

    /**
     * 新增合同
     * 
     * @param busContract 合同
     * @return 结果
     */
    @Override
    public int insertBusContract(BusContract busContract)
    {
        busContract.setCreateTime(DateUtils.getNowDate());
        return busContractMapper.insertBusContract(busContract);
    }

    /**
     * 修改合同
     * 
     * @param busContract 合同
     * @return 结果
     */
    @Override
    public int updateBusContract(BusContract busContract)
    {
        busContract.setUpdateTime(DateUtils.getNowDate());
        return busContractMapper.updateBusContract(busContract);
    }

    /**
     * 批量删除合同
     * 
     * @param contractIds 需要删除的合同主键
     * @return 结果
     */
    @Override
    public int deleteBusContractByContractIds(Long[] contractIds)
    {
        return busContractMapper.deleteBusContractByContractIds(contractIds);
    }

    /**
     * 删除合同信息
     * 
     * @param contractId 合同主键
     * @return 结果
     */
    @Override
    public int deleteBusContractByContractId(Long contractId)
    {
        return busContractMapper.deleteBusContractByContractId(contractId);
    }

    /**
     * 检查合同编号是否重复
     * @param busContract
     * @return
     */
    @Override
    public boolean checkContractExists(BusContract busContract) {
        return busContractMapper.checkContractExists(busContract) > 0;
    }

    @Override
    public BusContractDetail getContractDetail(Long contractId){
        return busContractMapper.getContractDetail(contractId);
    }

    @Override
    public List<BusContractDetail> getContractListDetail(BusContract busContract){
        return busContractMapper.getContractListDetail(busContract);
    }
    @Override
    public List<BusContract> getRemindContractList(){
        return busContractMapper.getRemindContractList();
    }
//    @Override
//    public List<BusExportCustomer> getExportCustomerList(){
//        return busContractMapper.getExportCustomerList();
//    }
//    @Override
//    public List<BusExportContract> getExportContractList(){
//        return busContractMapper.getExportContractList();
//    }

    @Override
    public AjaxResult exportContractInfo(Long customerId){
        List<BusExportCustomer> listCustomer = busContractMapper.getExportCustomerList(customerId);
        List<BusExportContract> listContract = busContractMapper.getExportContractList(customerId);

        ExcelContractExport excelContractExport = new ExcelContractExport();

        return excelContractExport.exportExcel(listCustomer,listContract,"合同信息");
        //return "";
    }

//    private String export(){
//
//    }
//    private void createWorkbook()
//    {
//        new SXSSFWorkbook(500);
//    }



}

class ExcelContractExport{
    private static final Logger log = LoggerFactory.getLogger(ExcelContractExport.class);

    /**
     * Excel sheet最大行数，默认65536
     */
    public static final int sheetSize = 65536;

    /**
     * 工作表名称
     */
    private String sheetName;

    /**
     * 工作薄对象
     */
    private Workbook wb;

    /**
     * 工作表对象
     */
    private Sheet sheet;

    /**
     * 样式列表
     */
    private Map<String, CellStyle> styles;

    public ExcelContractExport(){
    }
    private void init( String sheetName)
    {
        this.sheetName = sheetName;
        createWorkbook();
    }
    private void createWorkbook()
    {
        this.wb = new SXSSFWorkbook(500);
    }

    public AjaxResult exportExcel(List<BusExportCustomer> listCustomer,List<BusExportContract> listContract, String sheetName)
    {
        this.init(sheetName);

        return exportExcel(listCustomer,listContract);
    }

    /**
     * 对list数据源将其里面的数据导入到excel表单
     *
     * @return 结果
     */
    private AjaxResult exportExcel(List<BusExportCustomer> listCustomer,List<BusExportContract> listContract)
    {
        OutputStream out = null;
        try
        {
            writeSheet(listCustomer,listContract);
            String filename = encodingFilename(sheetName);
            out = new FileOutputStream(getAbsoluteFile(filename));
            wb.write(out);
            return AjaxResult.success(filename);
        }
        catch (Exception e)
        {
            //log.error("导出Excel异常{}", e.getMessage());
            throw new UtilException("导出Excel失败，请联系网站管理员！");
        }
        finally
        {
            IOUtils.closeQuietly(wb);
            IOUtils.closeQuietly(out);
        }
    }
    private void writeSheet(List<BusExportCustomer> listCustomer,List<BusExportContract> listContract)
    {
        createSheet();
        // 产生一行
        Row row = sheet.createRow(0);
        createHeader(row);
        if(listContract.size() == 0)
            return;
        fillExcelData(listCustomer,listContract);
    }

    private void createSheet()
    {
        this.sheet = wb.createSheet();
        this.styles = createStyles(wb);
        // 设置工作表的名称.
        wb.setSheetName(0, sheetName);
    }

    private void createHeader(Row row){
        this.createCell(row,"合同编号",16, 0);
        this.createCell(row,"客户名称",16, 1);
        this.createCell(row,"开票内容",16, 2);
        this.createCell(row,"完工进度",16, 3);
        this.createCell(row,"合同金额",16, 4);
        this.createCell(row,"已开票金额",16, 5);

        this.createCell(row,"未开票金额",16, 6);
        this.createCell(row,"已开票比例",16, 7);
        this.createCell(row,"已收货款",16, 8);
        this.createCell(row,"已收款比例",16, 9);
        this.createCell(row,"应收/预收款",16, 10);
        this.createCell(row,"所欠货款",16, 11);

        this.createCell(row,"交货日期",16, 12);
        this.createCell(row,"验收日期",16, 13);
        this.createCell(row,"质保日期",16, 14);
        this.createCell(row,"开票日期",16, 15);
        this.createCell(row,"发票号码",16, 16);
        this.createCell(row,"付款方式",16, 17);

        this.createCell(row,"有无送货单或验收单",16, 18);
        this.createCell(row,"负责人",16, 19);
        this.createCell(row,"催款情况",16, 20);
        this.createCell(row,"客户联系人",16, 21);
    }

    private Map<String, CellStyle> createStyles(Workbook wb)
    {
        // 写入各条记录,每条记录对应excel表中的一行
        Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
        CellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderLeft(BorderStyle.THIN);
        style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderTop(BorderStyle.THIN);
        style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderBottom(BorderStyle.THIN);
        style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());

        Font dataFont = wb.createFont();
        dataFont.setFontName("Arial");
        dataFont.setFontHeightInPoints((short) 10);
        style.setFont(dataFont);
        styles.put("data", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        Font headerFont = wb.createFont();
        headerFont.setFontName("Arial");
        headerFont.setFontHeightInPoints((short) 10);
        headerFont.setBold(true);
        headerFont.setColor(IndexedColors.WHITE.getIndex());
        style.setFont(headerFont);
        styles.put("header", style);

        style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        Font totalFont = wb.createFont();
        totalFont.setFontName("Arial");
        totalFont.setFontHeightInPoints((short) 10);
        style.setFont(totalFont);
        styles.put("total", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(HorizontalAlignment.LEFT);
        styles.put("data_left", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(HorizontalAlignment.CENTER);
        styles.put("data_center", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(HorizontalAlignment.RIGHT);
        styles.put("data_right", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(HorizontalAlignment.CENTER);
        DataFormat df = wb.createDataFormat();
        style.setDataFormat(df.getFormat("#,##0.00_ ;[RED]-#,##0.00 "));
        styles.put("data_number", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0%"));
        styles.put("data_proportion", style);

        Font subtotalFont = wb.createFont();
        subtotalFont.setFontName("Arial");
        subtotalFont.setFontHeightInPoints((short) 10);
        subtotalFont.setBold(true);
        subtotalFont.setColor(IndexedColors.BLACK.getIndex());

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setFont(subtotalFont);
        styles.put("subtotal_blank", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setFont(subtotalFont);
        style.setDataFormat(df.getFormat("#,##0.00_);[RED](#,##0.00)"));
        styles.put("subtotal_number", style);

        Font sumFont = wb.createFont();
        sumFont.setFontName("Arial");
        sumFont.setFontHeightInPoints((short) 10);
        sumFont.setBold(true);
        sumFont.setColor(IndexedColors.BLACK.getIndex());

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setFont(sumFont);
        styles.put("total_blank", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setFont(sumFont);
        style.setDataFormat(df.getFormat("#,##0.00_);[RED](#,##0.00)"));
        styles.put("total_number", style);

        return styles;
    }

    private Cell createCell(Row row,String title,double width, int column)
    {
        // 创建列
        Cell cell = row.createCell(column);
        // 写入列信息
        cell.setCellValue(title);

        cell.setCellStyle(styles.get("header"));

        sheet.setColumnWidth(column, (int) ((width + 0.72) * 256));

        return cell;
    }

    private void fillExcelData(List<BusExportCustomer> listCustomer,List<BusExportContract> listContract)
    {
        int totalIndex = 1;
        int beginIndex = 1;
        int endIndex = 1;

        BigDecimal totalContractSum = new BigDecimal("0");
        BigDecimal totalInvoiceSum= new BigDecimal("0");
        BigDecimal totalNoInvoiceSum= new BigDecimal("0");
        BigDecimal totalCollectionSum= new BigDecimal("0");
        BigDecimal totalReceivables = new BigDecimal("0");
        BigDecimal totalDebt = new BigDecimal("0");

        BigDecimal subtotalContractSum = new BigDecimal("0");
        BigDecimal subtotalInvoiceSum= new BigDecimal("0");
        BigDecimal subtotalNoInvoiceSum= new BigDecimal("0");
        BigDecimal subtotalCollectionSum= new BigDecimal("0");
        BigDecimal subtotalReceivables = new BigDecimal("0");
        BigDecimal subtotalDebt = new BigDecimal("0");


        Row row = null;
        for(int i =0;i<listCustomer.size();i++){
            Long customerId = listCustomer.get(i).getCustomerId();
            List<BusExportContract> listCustomerContract =  listContract.stream().filter(x -> x.getCustomerId() == customerId)
                    .collect(Collectors.toList());

            beginIndex = totalIndex;
            endIndex = beginIndex + listCustomerContract.size() - 1;

            subtotalContractSum = new BigDecimal("0");
            subtotalInvoiceSum= new BigDecimal("0");
            subtotalNoInvoiceSum= new BigDecimal("0");
            subtotalCollectionSum= new BigDecimal("0");
            subtotalReceivables = new BigDecimal("0");
            subtotalDebt = new BigDecimal("0");

            for(int j =0;j<listCustomerContract.size();j++){
                row = sheet.createRow(totalIndex++);

                // 设置行高
                //row.setHeight((short)(16*20));

                BusExportContract t = listCustomerContract.get(j);

                this.addCell(row,"data_left",Excel.ColumnType.STRING,"", t.getContractNo(), 0);
                this.addCell(row,"data_left",Excel.ColumnType.STRING,"", j==0?listCustomer.get(i).getCustomerName():"", 1);
                this.addCell(row,"data_left",Excel.ColumnType.STRING,"", t.getInvoiceContent(), 2);
                this.addCell(row,"data_center",Excel.ColumnType.STRING,"", convertDictByExp(t.getCompleteType(),"bus_complete_type",""), 3);
                this.addCell(row,"data_number",Excel.ColumnType.NUMERIC,"", t.getContractSum(), 4);
                this.addCell(row,"data_number",Excel.ColumnType.NUMERIC,"", t.getInvoiceSum(), 5);
                this.addCell(row,"data_number",Excel.ColumnType.NUMERIC,"", t.getContractSum().subtract(t.getInvoiceSum()), 6);

                if(t.getContractSum().compareTo(BigDecimal.ZERO) == 0) {
                    this.addCell(row,"data_proportion",Excel.ColumnType.NUMERIC,"", 0, 7);

                    this.addCell(row,"data_number",Excel.ColumnType.NUMERIC,"", t.getCollectionSum(), 8);

                    this.addCell(row,"data_proportion",Excel.ColumnType.NUMERIC,"", 0, 9);
                }
                else{
                    Double invoiceProportion = t.getInvoiceSum().divide(t.getContractSum(), 2,BigDecimal.ROUND_HALF_UP).doubleValue() ;
                    this.addCell(row,"data_proportion",Excel.ColumnType.NUMERIC,"", invoiceProportion, 7);

                    this.addCell(row,"data_number",Excel.ColumnType.NUMERIC,"", t.getCollectionSum(), 8);

                    Double collectionProportion =  t.getCollectionSum().divide(t.getContractSum(), 2,BigDecimal.ROUND_HALF_UP).doubleValue() ;
                    this.addCell(row,"data_proportion",Excel.ColumnType.NUMERIC,"", collectionProportion, 9);
                }

                this.addCell(row,"data_number",Excel.ColumnType.NUMERIC,"",t.getInvoiceSum().subtract(t.getCollectionSum()) , 10);
                this.addCell(row,"data_number",Excel.ColumnType.NUMERIC,"",t.getContractSum().subtract(t.getCollectionSum()) , 11);

                this.addCell(row,"data_left",Excel.ColumnType.STRING,"yyyy/MM/dd",t.getDeliveryDate() , 12);
                this.addCell(row,"data_left",Excel.ColumnType.STRING,"yyyy/MM/dd",t.getAcceptanceDate() , 13);
                this.addCell(row,"data_left",Excel.ColumnType.STRING,"yyyy/MM/dd",t.getWarrantyDate() , 14);

                this.addCell(row,"data_left",Excel.ColumnType.STRING,"",t.getInvoiceDate() , 15);
                this.addCell(row,"data_left",Excel.ColumnType.STRING,"",t.getInvoiceNo() , 16);
                this.addCell(row,"data_left",Excel.ColumnType.STRING,"",convertDictByExp(listCustomer.get(i).getPaymentType(),"bus_payment_type","") , 17);

                this.addCell(row,"data_left",Excel.ColumnType.STRING,"",t.getCountDelivery() >0?"送货单":"", 18);
                this.addCell(row,"data_left",Excel.ColumnType.STRING,"",t.getDirector(), 19);
                this.addCell(row,"data_left",Excel.ColumnType.STRING,"",t.getReminderContent(), 20);

                this.addCell(row,"data_left",Excel.ColumnType.STRING,"",j==0?listCustomer.get(i).getCustomerContact():"", 21);

                subtotalContractSum = subtotalContractSum.add(t.getContractSum());
                totalContractSum= totalContractSum.add(t.getContractSum());

                subtotalInvoiceSum = subtotalInvoiceSum.add(t.getInvoiceSum());
                totalInvoiceSum = totalInvoiceSum.add(t.getInvoiceSum());

                subtotalNoInvoiceSum = subtotalNoInvoiceSum.add(t.getContractSum().subtract(t.getInvoiceSum()));
                totalNoInvoiceSum = totalNoInvoiceSum.add(t.getContractSum().subtract(t.getInvoiceSum()));

                subtotalCollectionSum = subtotalCollectionSum.add(t.getCollectionSum());
                totalCollectionSum = totalCollectionSum.add(t.getCollectionSum());

                subtotalReceivables = subtotalReceivables.add(t.getInvoiceSum().subtract(t.getCollectionSum()));
                totalReceivables = totalReceivables.add(t.getInvoiceSum().subtract(t.getCollectionSum()));

                subtotalDebt = subtotalDebt.add(t.getContractSum().subtract(t.getCollectionSum()));
                totalDebt = totalDebt.add(t.getContractSum().subtract(t.getCollectionSum()));
            }
            if(beginIndex != endIndex) {
                CellRangeAddress region = new CellRangeAddress(beginIndex, endIndex, 1, 1);
                sheet.addMergedRegion(region);
                CellRangeAddress region2 = new CellRangeAddress(beginIndex, endIndex, 21, 21);
                sheet.addMergedRegion(region2);
            }
            // 创建小计行
            row = sheet.createRow(totalIndex++);

            this.addCell(row,"subtotal_blank",Excel.ColumnType.STRING,"", "", 0);
            this.addCell(row,"subtotal_blank",Excel.ColumnType.STRING,"", "", 1);
            this.addCell(row,"subtotal_blank",Excel.ColumnType.STRING,"","", 2);
            this.addCell(row,"subtotal_blank",Excel.ColumnType.STRING,"", "", 3);
            this.addCell(row,"subtotal_number",Excel.ColumnType.NUMERIC,"", subtotalContractSum, 4);
            this.addCell(row,"subtotal_number",Excel.ColumnType.NUMERIC,"", subtotalInvoiceSum, 5);
            this.addCell(row,"subtotal_number",Excel.ColumnType.NUMERIC,"", subtotalNoInvoiceSum, 6);
            this.addCell(row,"subtotal_blank",Excel.ColumnType.STRING,"", "", 7);
            this.addCell(row,"subtotal_number",Excel.ColumnType.NUMERIC,"", subtotalCollectionSum, 8);
            this.addCell(row,"subtotal_blank",Excel.ColumnType.STRING,"", "", 9);
            this.addCell(row,"subtotal_number",Excel.ColumnType.NUMERIC,"", subtotalReceivables, 10);
            this.addCell(row,"subtotal_number",Excel.ColumnType.NUMERIC,"", subtotalDebt, 11);
            this.addCell(row,"subtotal_blank",Excel.ColumnType.STRING,"", "", 12);
            this.addCell(row,"subtotal_blank",Excel.ColumnType.STRING,"", "", 13);
            this.addCell(row,"subtotal_blank",Excel.ColumnType.STRING,"", "", 14);
            this.addCell(row,"subtotal_blank",Excel.ColumnType.STRING,"", "", 15);
            this.addCell(row,"subtotal_blank",Excel.ColumnType.STRING,"", "", 16);
            this.addCell(row,"subtotal_blank",Excel.ColumnType.STRING,"", "", 17);
            this.addCell(row,"subtotal_blank",Excel.ColumnType.STRING,"", "", 18);
            this.addCell(row,"subtotal_blank",Excel.ColumnType.STRING,"", "", 19);
            this.addCell(row,"subtotal_blank",Excel.ColumnType.STRING,"", "", 20);
            this.addCell(row,"subtotal_blank",Excel.ColumnType.STRING,"", "", 21);

            CellRangeAddress regionSubTotal = new CellRangeAddress(endIndex+1, endIndex+1, 0, 2);
            sheet.addMergedRegion(regionSubTotal);

        }

        row = sheet.createRow(totalIndex++);

        this.addCell(row,"total_blank",Excel.ColumnType.STRING,"", "总计", 0);
        this.addCell(row,"total_blank",Excel.ColumnType.STRING,"", "", 1);
        this.addCell(row,"total_blank",Excel.ColumnType.STRING,"","", 2);
        this.addCell(row,"total_blank",Excel.ColumnType.STRING,"", "", 3);
        this.addCell(row,"total_number",Excel.ColumnType.NUMERIC,"", totalContractSum, 4);
        this.addCell(row,"total_number",Excel.ColumnType.NUMERIC,"", totalInvoiceSum, 5);
        this.addCell(row,"total_number",Excel.ColumnType.NUMERIC,"", totalNoInvoiceSum, 6);
        this.addCell(row,"total_blank",Excel.ColumnType.STRING,"", "", 7);
        this.addCell(row,"total_number",Excel.ColumnType.NUMERIC,"", totalCollectionSum, 8);
        this.addCell(row,"total_blank",Excel.ColumnType.STRING,"", "", 9);
        this.addCell(row,"total_number",Excel.ColumnType.NUMERIC,"", totalReceivables, 10);
        this.addCell(row,"total_number",Excel.ColumnType.NUMERIC,"", totalDebt, 11);
        this.addCell(row,"total_blank",Excel.ColumnType.STRING,"", "", 12);
        this.addCell(row,"total_blank",Excel.ColumnType.STRING,"", "", 13);
        this.addCell(row,"total_blank",Excel.ColumnType.STRING,"", "", 14);
        this.addCell(row,"total_blank",Excel.ColumnType.STRING,"", "", 15);
        this.addCell(row,"total_blank",Excel.ColumnType.STRING,"", "", 16);
        this.addCell(row,"total_blank",Excel.ColumnType.STRING,"", "", 17);
        this.addCell(row,"total_blank",Excel.ColumnType.STRING,"", "", 18);
        this.addCell(row,"total_blank",Excel.ColumnType.STRING,"", "", 19);
        this.addCell(row,"total_blank",Excel.ColumnType.STRING,"", "", 20);
        this.addCell(row,"total_blank",Excel.ColumnType.STRING,"", "", 21);

        CellRangeAddress regionSubTotal = new CellRangeAddress(totalIndex-1, totalIndex-1, 0, 2);
        sheet.addMergedRegion(regionSubTotal);

        sheet.createFreezePane(3, 1, 3, 1);

    }
    public Cell addCell(Row row,String style, Excel.ColumnType columnType,String dateFormat, Object value, int column)
    {
        Cell cell = null;
        try
        {
            // 创建cell
            cell = row.createCell(column);

            cell.setCellStyle(styles.get(style));

            if (StringUtils.isNotEmpty(dateFormat) && StringUtils.isNotNull(value))
            {
                cell.setCellValue(DateUtils.parseDateToStr(dateFormat, (Date) value));
                return cell;
            }
            if (Excel.ColumnType.STRING == columnType)
            {
                cell.setCellValue(StringUtils.isNull(value) ? "" : value.toString());
                return cell;
            }
            if (Excel.ColumnType.NUMERIC == columnType)
            {
                if (StringUtils.isNotNull(value))
                {
                    cell.setCellValue(StringUtils.contains(Convert.toStr(value), ".") ? Convert.toDouble(value) : Convert.toInt(value));
                    return cell;
                }
            }
        }
        catch (Exception e)
        {
            log.error("导出Excel失败{}", e);
        }
        return cell;
    }




    public String encodingFilename(String filename)
    {
        filename = UUID.randomUUID().toString() + "_" + filename + ".xlsx";
        return filename;
    }

    /**
     * 获取下载路径
     *
     * @param filename 文件名称
     */
    public String getAbsoluteFile(String filename)
    {
        String downloadPath = RuoYiConfig.getDownloadPath() + filename;
        File desc = new File(downloadPath);
        if (!desc.getParentFile().exists())
        {
            desc.getParentFile().mkdirs();
        }
        return downloadPath;
    }

    private String convertDictByExp(String dictValue, String dictType, String separator)
    {
        return DictUtils.getDictLabel(dictType, dictValue, separator);
    }
}
